Date Arithmetic Uses and Syntax

 

A command yields a specific date and may be used without any qualifiers. The following table displays the commands that are supported by ActiveBatch’s Date Arithmetic facility.

 

Command	Description
TODAY	Today’s date
TOMORROW	Tomorrow’s date
YESTERDAY	Yesterday’s date
WEEKDAY	Day of Week (Monday through Friday)
WEEKENDDAY	Day of Weekend (Saturday, Sunday)
BOM	Beginning of Month
EOM	End of Month
BOW	Beginning of Week
EOW	End of Week
BOY	Beginning of Year
EOY	End of Year
BOQ	Beginning of Quarter
EOQ	End of Quarter
YEAR	Year
MONDAY – SUNDAY	Day tags for all seven days
NOW	Time portion of current day
MIDNIGHT	Midnight of current day
NOON	Noon of current day

 

The commands above, unless otherwise specified, assume today’s system date. The system date is the date supplied by the platform operating system. While several of the above commands are self-explanatory, there are several that do require an explanation.

 

By default, BOW is the beginning of the week, which is Monday. EOW is the end of the week, which is Sunday. BOQ is the beginning of the quarter and assumes a calendar year, while EOQ is the end of the quarter (and makes the same assumption). MONDAY through SUNDAY commands use an integral week. For example, if today is October 1st and that date is a Wednesday, the MONDAY tag would be September 29th, TUESDAY would be September 30th and WEDNESDAY would be October 1st, etc.

 

There is one special command that is missing from the table. DATE is a tag that can be used depending on the context of the operation. DATE represents an ActiveBatch supplied or customer supplied date.

 

Qualifiers perform the actual date arithmetic and computation. Zero or more qualifiers can be specified and the date arithmetic is performed from left to right eventually yielding a date. The date is substituted based on the date format. By default, dates are displayed as ddmmmyy. The following table displays supported qualifiers.

 

Qualifier	Description
/±DAY=n	Add or subtract days. If n is omitted, 1 is used.
/±WEEK=n	Add or subtract weeks. If n is omitted, 1 is used.
/±MONTH=n	Add or subtract month. If n is omitted, 1 is used.
/±QUARTER=n	Add or subtract quarters. If n is omitted, 1 is used.
/±YEAR=n	Add or subtract years. If n is omitted. 1 is used.
/±MONDAY=n	Add or subtract Mondays (you can replace MONDAY with each day). If n is omitted, 1 is used.
/NEXT	Move forward 1 unit (unit depends on current context)
/LAST	Move backward 1 unit (unit depends on current context)
/TIME=hh:mm:ss	Used with a valid command to set a specific time.
/TIMEZONE=time-zone-name	Used to establish a time zone that is evaluated based on the current time. The “timezone-name” is case-insensitive but if it contains embedded spaces it must be enclosed in double-quotes.  The timezone name is one taken from a list of timezone-names that can be found in the registry:  HKLM\Software\Microsoft\Windows NT\CurrentVersion\Time Zones. If this qualifier is omitted the current local machine time is used.
/±HOUR=n	Add or subtract hours. If n is omitted, 1 is used.
/±MINUTE=n	Add or subtract minutes. If n is omitted, 1 is used.
/ROUND=hh:mm:ss	Rounds up/down to the nearest hours, minutes and seconds.
/SUPPRESS	Suppress leading characters
/FORMAT=format-spec /SETDATE=DDMMMYYYY[HHMI]	Produces date using date specification string. Sets Today’s date. Useful when performing simulations and other purposes. If HHMI (hours and minutes) are omitted the default is 0000 (midnight)
/SETDATE_FORMAT=format-string	Allows you to change the input format for the /SETDATE qualifier. Especially useful if you want to populate a date from the @JobScheduler.CurrentBusinessDate variable which has a format of “mm/dd/yyyy”

 

The qualifiers found in the above table add or subtract to the starting date to produce a new date that is then used in any additional calculations. For example, <BOM/-WEDNESDAY> means the last Wednesday of the previous month. You can also specify a value without a qualifier in which case “days” are assumed.

For example, <TODAY/+5> means 5 days from the current date.

 

Examples:

 

<BOY/+MONTH=5/-DAY/-MONDAY> would yield Memorial Day (US holiday, which is the last Monday of every May).

 

<BOM/+MONDAY>                    would yield the first Monday in month.

 

<TODAY/+FRIDAY>                    would yield next Friday.

 

Format Qualifier	Description
/FORMAT=ddmmmyy	Default date format
/FORMAT=dd	Numeric day of month
/FORMAT=ddd	Abbreviated day of week (e.g. Mon, Tue, etc.)
/FORMAT=dddd	Alphabetic day of week (e.g. Monday, Tuesday, etc.)
/FORMAT=mm	Numeric month number
/FORMAT=mmm	Abbreviated alphabetic month (e.g. Jan, Feb, etc.)
/FORMAT=mmmm	Alphabetic month (e.g. January, February, etc.)
/FORMAT=yy	Two digit numeric year
/FORMAT=yyyy	Four digit numeric year
/FORMAT=jjj	Julian day of year (001-365 or 366)
/FORMAT=wi	Week of year (per ISO 8601)
/FORMAT=ww	Week of year (performs a Common Week calculation)
/FORMAT=hh	Hour (00-23)
/FORMAT=mi	Minute (00-59)
/FORMAT=ss	Second (00-59)
/FORMAT=cc	Fraction of second (00-99)

 

Note: You can also format a date with slashes (/) however the string must be quoted (i.e. /FORMAT=”mm/dd/yyyy”).

 

When specifying a Date Arithmetic expression, you may sometimes want to add logic so that if a calculated date falls on a specific day or date, that additional action should be taken. Consider the following example: <BOM/+BUSDAY=4>

 

This expression means the fifth business day of the month (remember the value is zero based). However, what if the fifth business day of a month falls on a Tuesday and I need the day to become Wednesday?  That is where the /IF qualifier becomes handy. The /IF qualifier uses the following syntax:  /IF[NOT]<condition>=<operation>

 

Where:

 

<condition> can be any standard Date Arithmetic verb (e.g. TUESDAY), a specific date (MMDDYYYY) or Calendar tag.

For standard verbs a relative number can be added at the end, e.g. TUESDAY2 means the second Tuesday of the month.

 

<operation> is [+][-]numeric value or NEXT or LAST. The numeric value’s context is inherited based on the previous verbs

and qualifiers.

 

For example, <TODAY/+BUSDAY=1/IFWEDNESDAY=+3…> means to add three (3) “business days” to the end result. The “business days” context is due to the previous use of /BUSDAY.

 

If you wanted calendar days then <TODAY/+DAY=1/+WEDNESDAY=3> could have been specified.

 

More examples:

 

<BOM/+BUSDAY=4/IFTUESDAY=+1> means you want the fifth business day of the month unless that day is a Tuesday in which case bump the day to Wednesday (assuming Wednesday is a business day).

 

<BOM/+BUSDAY=4/IFTUESDAY=-1> means you want the fifth business day of the month unless that day is a Tuesday in which case bump to the previous day of Monday (assuming Monday is a business day).

 

You can add as many /IF qualifiers as your Date Expression logic requires.

 

When Date Arithmetic is used as part of a custom specification in the Schedule Object object, you can specify any valid Date Arithmetic tags - except /FORMAT. Typically this Schedule option is used when the Schedule object's date options do not generate the trigger dates you need. See the example below. This image was taken from the Day Specification tab of a Schedule object. The date arithmetic syntax in the Custom Specification property equates to the last Monday in May. This indicates the yearly date a Job or Plan using this Schedule object will trigger on.

 

 

You can use date arithmetic as part of a Process or Script Job type by entering a date arithmetic expression in the parameters property. You can also create a date arithmetic active variable, and reference the variable in the parameters property.

 

Below is an Active Variable defined using date arithmetic. It returns a date that is 2 days from the current date.

 

 

Below is a image of a Script type Job. It is using date arithmetic expressions as the first two parameters, followed by a variable (TodayPlusTwoDay) as the last parameter. The ${} syntax is used to reference constant and active variables.  As a best practice, use the date arithmetic variable when you have multiple Jobs using the same date arithmetic syntax. Should anything change about the date arithmetic syntax (for example, the format), you only have to change it in one place - on the variable.

 

 

Below is the Job log file that is generated after the above script runs. The default date arithmetic format is ddmmmyy. You can specify another format using the /FORMAT command.  For example, <Today/FORMAT="dd/mm/yyyy">. Quotes are required when there is a "/" slash in the date format.